package database.magazina;

import database.common.ConnectionFactory;
import model.app.Person;
import model.magazina.Furnizim;
import model.magazina.FurnizimDetail;

import java.sql.*;
import java.sql.Date;
import java.util.*;

/**
 * Created with IntelliJ IDEA.
 * Author: Kostika Gorica
 * User:   kosta
 * Date:   1/3/13
 * Time:   8:10 PM
 */
public class FurnizimDao {
    private static final String FURNIZIM_INSERT =
            "INSERT INTO magazina.flete_furnizim (id, furnitori,operatori,paguar,data_furnizimit,data_pageses,shuma_fatures,taksa,magazina_id,shenime ) VALUES (?,?,?,?,?,?,?,?,?,?)";
    private static final String FURNIZIM_UPDATE =
            "UPDATE magazina.flete_furnizim SET furnitori=?,operatori=?,paguar=?,data_furnizimit=?,data_pageses=?,shuma_fatures=?,taksa=?,magazina_id=?,shenime=?  WHERE id  = ?";
    private static final String SELECT_SINGLE_FURNIZIM =
            "SELECT id, furnitori,operatori,paguar,data_furnizimit,data_pageses,shuma_fatures,taksa,magazina_id,shenime FROM magazina.flete_furnizim WHERE id = ? ";
    private static final String FURNIZIM_DETAIL_INSERT =
            "INSERT INTO magazina.furnizim (fleta_id, artikulli_id, sasia, cmimi) VALUES (?,?,?,?)";
    private static final String SELECT_FURNIZIM_DETAILS =
            "SELECT id, fleta_id, artikulli_id, sasia, cmimi FROM magazina.furnizim WHERE fleta_id=?";
    private static final String SELECT_FURNIZIME_UNIT_DATE_XML =
            "SELECT query_to_xml" +
            "('SELECT * from magazina.flete_furnizim WHERE magazina_id='||?||' AND date(data_furnizimit)>=date('||?||') AND date(data_furnizimit)<=date('||?||') ORDER BY data_furnizimit '," +
            "true,false,'')";

    private static int insert(Furnizim furnizim, Connection connection) throws SQLException {
        PreparedStatement statement = null;
        int rows = 0;
        if (connection == null || connection.isClosed()){
            return -1;
        }
        try {
            statement = connection.prepareStatement(FURNIZIM_INSERT);
            statement.setInt(1, furnizim.getId());
            statement.setInt(2, furnizim.getFurnitoriID());
            statement.setInt(3, furnizim.getOperatoriID());
            statement.setInt(4, furnizim.getPaguar());
            statement.setTimestamp(5, new Timestamp(furnizim.getDataFurnizimit().getTime()));
            if (furnizim.getDataPageses()!=null){
                statement.setTimestamp(6, new Timestamp(furnizim.getDataPageses().getTime()));
            } else {
                statement.setNull(6, Types.TIMESTAMP);
            }
            statement.setDouble(7, furnizim.getShumaFatures());
            statement.setDouble(8, furnizim.getTaksa());
            statement.setInt(9, furnizim.getMagazinaID());
            statement.setString(10, furnizim.getShenime());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    private static int insertDetail(FurnizimDetail detail, Connection connection) throws SQLException {
        PreparedStatement statement = null;
        int rows = 0;
        if (connection == null || connection.isClosed()){
            return -1;
        }
        try {
            statement = connection.prepareStatement(FURNIZIM_DETAIL_INSERT);
            statement.setInt(1, detail.getFletaId());
            statement.setInt(2, detail.getArtikulliId());
            statement.setDouble(3, detail.getSasia());
            statement.setDouble(4, detail.getCmimi());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static boolean saveFurnizim(Furnizim furnizim){
        Connection connection = ConnectionFactory.getConnection();
        Savepoint startTransaction = null;
        try {
            connection.setAutoCommit(false);
            startTransaction = connection.setSavepoint();
            furnizim.setId(getNextID());
            int row = insert(furnizim, connection);
            if (row != 1){
                connection.rollback(startTransaction);
                return false;
            }
            List<FurnizimDetail> details=furnizim.getDetailList();
            for (FurnizimDetail fdt:details){
                fdt.setFletaId(furnizim.getId());
                row = insertDetail(fdt,connection);
                if (row!=1){
                    connection.rollback(startTransaction);
                    return false;
                }
            }
            connection.commit();
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                connection.rollback(startTransaction);
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
            return false;
        } finally {
            try{
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return true;
    }

    public static int update(Furnizim furnizim){
        Connection connection = null;
        PreparedStatement statement = null;
        int rows = 0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(FURNIZIM_UPDATE);
            if (furnizim.getId()<1){
                return -1;
            }
            statement.setInt(1, furnizim.getFurnitoriID());
            statement.setInt(2, furnizim.getOperatoriID());
            statement.setInt(3, furnizim.getPaguar());
            statement.setTimestamp(4, new Timestamp(furnizim.getDataFurnizimit().getTime()));
            if (furnizim.getDataPageses()!=null){
                statement.setTimestamp(5, new Timestamp(furnizim.getDataPageses().getTime()));
            } else {
                statement.setNull(5, Types.TIMESTAMP);
            }
            statement.setDouble(6, furnizim.getShumaFatures());
            statement.setDouble(7, furnizim.getTaksa());
            statement.setInt(8, furnizim.getMagazinaID());
            statement.setString(9, furnizim.getShenime());
            statement.setInt(10, furnizim.getId());
            rows = statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return rows;
    }

    public static Furnizim selectSingleFurnizim(int id){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Furnizim furnizim = null;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_SINGLE_FURNIZIM);
            statement.setInt(1, id);
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                furnizim = new Furnizim();
                furnizim.setId(resultSet.getInt(1));
                furnizim.setFurnitoriID(resultSet.getInt(2));
                furnizim.setOperatoriID(resultSet.getInt(3));
                furnizim.setPaguar(resultSet.getInt(4));
                furnizim.setDataFurnizimit(resultSet.getTimestamp(5));
                furnizim.setDataPageses(resultSet.getTimestamp(6));
                furnizim.setShumaFatures(resultSet.getDouble(7));
                furnizim.setTaksa(resultSet.getDouble(8));
                furnizim.setMagazinaID(resultSet.getInt(9));
                furnizim.setShenime(resultSet.getString(10));
                furnizim.setDetailList(getFurnizimDetails(furnizim.getId()));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (resultSet!=null) resultSet.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return furnizim;
    }

    public static List<FurnizimDetail> getFurnizimDetails(int furnizimID){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        List<FurnizimDetail> detailList = new ArrayList<FurnizimDetail>();
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement(SELECT_FURNIZIM_DETAILS);
            statement.setInt(1, furnizimID);
            resultSet = statement.executeQuery();
            while (resultSet.next()){
                FurnizimDetail detail = new FurnizimDetail();
                detail.setId(resultSet.getInt(1));
                detail.setFletaId(resultSet.getInt(2));
                detail.setArtikulliId(resultSet.getInt(3));
                detail.setSasia(resultSet.getDouble(4));
                detail.setCmimi(resultSet.getDouble(5));
                detailList.add(detail);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return detailList;
    }

    public static String selectFurnizimeByUnitAndDate(int magazina, java.util.Date startDate,java.util.Date endDate){
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        String furnizimList = "";
        try {
            connection = ConnectionFactory.getConnection();
            //TODO if magazina has childs
            statement = connection.prepareStatement(SELECT_FURNIZIME_UNIT_DATE_XML);
            statement.setInt(1, magazina);
            statement.setDate(2, new Date(startDate.getTime()));
            statement.setDate(3, new Date(endDate.getTime()));
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                furnizimList = resultSet.getString(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (statement!=null) statement.close();
                if (resultSet!=null) resultSet.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return furnizimList;
    }

    private static int getNextID() {
        Connection connection = null;
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        int result =0;
        try {
            connection = ConnectionFactory.getConnection();
            statement = connection.prepareStatement("SELECT nextval('flete_furnizim_id_seq')");
            resultSet = statement.executeQuery();
            if (resultSet.next()){
                result = resultSet.getInt(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            try{
                if (resultSet!=null) resultSet.close();
                if (statement!=null) statement.close();
                if (connection!=null) connection.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return result;
    }

}
